package com.yenlien.traceyun.usercenter.dao;


import com.yenlien.traceyun.usercenter.entity.Company;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface CompanyDao extends BaseJpaDao<Company> {
    Company findById(String id);


    @Query(value = "SELECT * FROM t_company WHERE id = (SELECT parent_id FROM t_company WHERE id = ?1)", nativeQuery = true)
    Company findParentById(String id);

    public Company findByUserId(String userId);

    public Company findByPhone(String phone);

    public Company findByName(String name);

    public List<Company> findByType(String type);

    List<Company> findByCreatetimeBetween(String createtime, String createtime2);

    public List<Company> findByParentId(String parentId);

    List<Company> findByExamined(String examined);

    // 查询该组织下面的组织数量(包括自己)
    @Query(value = "SELECT * FROM t_company WHERE parent_id = ?1 OR id = ?1", nativeQuery = true)
    List<Company> companyNumber(String companyId);


    @Query(value = "SELECT c.* FROM t_app a inner join t_company c on c.app_id=a.id WHERE app_id = ?1", nativeQuery = true)
    List<Company> companyfindByAppId(String appId);

    @Query(value = "SELECT * FROM t_company WHERE id IN (SELECT tccv.company_id FROM t_company_customapp_version tccv WHERE tccv.custom_app_id = ?1)", nativeQuery = true)
    List<Company> getCompanyByCustomAppId(String customAppId);

    @Query(value = "SELECT COUNT(*) FROM t_company WHERE parent_id = ?1 ", nativeQuery = true)
    int getCountByParentId(String parentId);

    @Query(value = "SELECT COUNT(*) FROM t_user WHERE company_id = ?1 ", nativeQuery = true)
    int getCompAllUser(String companyId);

    @Query(value = "SELECT COUNT(tu.id) FROM t_company tc JOIN t_user tu ON tc.id = tu.company_id WHERE tc.parent_id = ?1 OR tc.id = ?1", nativeQuery = true)
    int getAllUser(String companyId);

    @Query(value = "select id,abbreviation from t_company where id in (select lf.company_id from t_bank_info b inner join t_loan_info lf on lf.bank_info_id=b.id where b.company_id=?1)", nativeQuery = true)
    List<Map<String, Object>> bankAndCompany(String companyId);
}
